package hesso.smartdraw.ch.db;

import hesso.smartdraw.ch.soap.SOAPHelper;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Vector;
import org.ksoap2.serialization.SoapObject;
import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class SyncDbAndDb extends Activity {

	private static SyncDbAndDb instance;
	SQLiteDatabase dbConn;
	Cursor a;
	Cursor b;
	Cursor c;
	Cursor d;
	Cursor e;
	
	private SyncDbAndDb(){
		dbConn = DBHelper.getInstance(this).getDbConn();
	}

	public static SyncDbAndDb getInstance(){
		if(instance == null){
			instance = new SyncDbAndDb();
			return instance;
		}
		else{
			return instance;
		}
	}
	
	public int sync(int buildingId){
		int versionAndroid;
		int versionWeb;
		a = DBHelper.getInstance(this).getBuildingByBuildingIdCursor(buildingId);
		
		startManagingCursor(a);
        a.moveToFirst();
        
        versionAndroid = a.getInt(2);
        
        Vector<SoapObject> buildingWeb = SOAPHelper.getBuildingById(buildingId);
        
        if(buildingWeb!=null) {
        	versionWeb = Integer.parseInt((String) buildingWeb.get(0).getProperty("lastChange"));
        }
        else {
        	versionWeb = 0;
        }
        if(versionAndroid > versionWeb) {
        	androidToWeb(buildingId);
        	return 1;
        }
        if(versionWeb > versionAndroid) {
        	webToAndroid(buildingId);
        	return 2;
        }
        return 0;
	}
	
	public void startProgram(int userId){
		dbConn.execSQL("DELETE FROM tbl_Layers");
		dbConn.execSQL("DELETE FROM tbl_Devices");
		dbConn.execSQL("DELETE FROM tbl_Units");
		dbConn.execSQL("DELETE FROM tbl_Inputs");
		dbConn.execSQL("DELETE FROM tbl_Outputs");
		
		getLayers();
		getDevices();
		getUnits();
		getInputs();
		getOutputs();

		a = DBHelper.getInstance(this).getBuildings();
		
		if(a.getCount() == 0){
			getBuildingsByUser(userId);
		}
	}
	
	public void webToAndroid(int buildingId){
		DBHelper.getInstance(this).deleteBuildingByBuildingIdAndroid(buildingId);
		
		a = DBHelper.getInstance(this).getLevelsByBuildingIdCursor(buildingId);
		
		startManagingCursor(a);
		a.moveToFirst();
		
		for(int i=0; i<a.getCount(); i++){
			DBHelper.getInstance(this).deleteLevelByLevelIdAndroid(a.getInt(0));
			
			b = DBHelper.getInstance(this).getRoomsByLevelIdCursor(a.getInt(0));
			
			startManagingCursor(b);
			b.moveToFirst();
			
			for(int j=0;  j<b.getCount(); j++){
				DBHelper.getInstance(this).deleteRoomByRoomIdAndroid(b.getInt(0));
				
				c = DBHelper.getInstance(this).getWallsByRoomIdCursor(b.getInt(0));
				
				startManagingCursor(c);
				c.moveToFirst();
				
				for(int k=0; k<c.getCount(); k++){
					DBHelper.getInstance(this).deleteWallByWallIdAndroid(c.getInt(0));
					
					d = DBHelper.getInstance(this).getInstalledByWallIdCursor(c.getInt(0));
					
					startManagingCursor(d);
					d.moveToFirst();
					
					for(int l=0; l<d.getCount(); l++){
						DBHelper.getInstance(this).deleteInstalledDeviceByInstalledIdAndroid(d.getInt(0));
						
						e = DBHelper.getInstance(this).getConsumationsByInstalledIdCursor(d.getInt(0));
						
						startManagingCursor(e);
						e.moveToFirst();
						
						for(int m=0; m<e.getCount(); m++){
							DBHelper.getInstance(this).deleteConsumationByConsumationIdAndroid(e.getInt(0));
							
							e.moveToNext();
						}
						
						d.moveToNext();
					}
					
					c.moveToNext();
				}
				
				b.moveToNext();
			}
			
			a.moveToNext();
		}
		
		getBuildingById(buildingId);
	}
	
	public void androidToWeb(int buildingId){
		//delete
		a = DBHelper.getInstance(this).getBuildingByBuildingIdCursor(buildingId);
		
		startManagingCursor(a);
        a.moveToFirst();

        try{
    		SOAPHelper.removeBuilding(a.getInt(0));
        }
        catch(Exception e){
        	
        }
        
        //insert
		SOAPHelper.addBuilding(a.getInt(0), a.getString(1), a.getInt(3), a.getInt(2));

		a = DBHelper.getInstance(this).getLevelsByBuildingIdCursor(a.getInt(0));
		
		startManagingCursor(a);
        a.moveToFirst();
        
        for(int i=0; i<a.getCount(); i++){
        	SOAPHelper.addLevel(a.getInt(0), a.getString(1), a.getInt(2), a.getInt(3));
        	
        	b = DBHelper.getInstance(this).getRoomsByLevelIdCursor(a.getInt(0));

    		startManagingCursor(b);
            b.moveToFirst();
            
            for(int j=0; j<b.getCount(); j++){
            	SOAPHelper.addRoom(b.getInt(0), b.getString(1), b.getInt(2));

            	c = DBHelper.getInstance(this).getWallsByRoomIdCursor(b.getInt(0));
            	
        		startManagingCursor(c);
                c.moveToFirst();
                
                for(int k=0; k<c.getCount(); k++){
                	SOAPHelper.addWall(c.getInt(0), c.getString(1), c.getInt(2), c.getInt(3), c.getInt(4), c.getInt(5), c.getInt(6), c.getInt(7), c.getInt(9), c.getInt(8));
                	
                	d = DBHelper.getInstance(this).getInstalledByWallIdCursor(c.getInt(0));

            		startManagingCursor(d);
                    d.moveToFirst();
                    
                    for(int l=0; l<d.getCount(); l++){
                    	SOAPHelper.addInstalled(d.getInt(0), d.getString(1), d.getInt(2), d.getInt(3), d.getInt(4), d.getInt(5), d.getInt(6), d.getInt(7));
                    	
                    	e = DBHelper.getInstance(this).getConsumationsByInstalledIdCursor(d.getInt(0));
                    	
                    	startManagingCursor(e);
                    	e.moveToFirst();
                    	
                    	for(int m=0; m<e.getCount(); m++){
                    		SOAPHelper.addConsumation(e.getInt(0), e.getInt(1), e.getInt(2), e.getInt(3));
                    		
                    		e.moveToNext();
                    	}
                    	
                    	d.moveToNext();
                    }
                	
                	c.moveToNext();
                }
            	
            	b.moveToNext();
            }
            
        	a.moveToNext();
        }
	}
	
	public void getLayers(){
		Vector<SoapObject> vectorLayers = SOAPHelper.getLayers();
        ArrayList<HashMap<String, Object>> myList = new ArrayList<HashMap<String, Object>>();
        
    	for(int i=0; i<vectorLayers.size(); i++){  		    		    		
    		HashMap<String, Object> layers = new HashMap<String, Object>();
    		layers.put("layerId", vectorLayers.get(i).getProperty("layerId"));
    		layers.put("layerName", vectorLayers.get(i).getProperty("layerName"));
    		myList.add(layers);
    	}	

    	for(int i=0; i<myList.size(); i++){
    		dbConn.execSQL("INSERT INTO tbl_Layers" +
    				" (layerId, layerName) VALUES" +
    				" ('" + myList.get(i).get("layerId") + "', '" + myList.get(i).get("layerName") + "')");
    	}
	}
	
	public void getDevices(){
		Vector<SoapObject> vectorDevices = SOAPHelper.getDevices();
        ArrayList<HashMap<String, Object>> myList = new ArrayList<HashMap<String, Object>>();
        
    	for(int i=0; i<vectorDevices.size(); i++){
    		HashMap<String, Object> devices = new HashMap<String, Object>();
    		devices.put("deviceId", vectorDevices.get(i).getProperty("deviceId"));
    		devices.put("deviceName", vectorDevices.get(i).getProperty("deviceName"));
    		devices.put("layerId", vectorDevices.get(i).getProperty("tbl_Layers_layerId"));
    		myList.add(devices);
    	}

    	for(int i=0; i<myList.size(); i++){
    		dbConn.execSQL("INSERT INTO tbl_Devices" +
    				" (deviceId, deviceName, tbl_Layers_layerId) VALUES" +
    				" ('" + myList.get(i).get("deviceId") + "', '" + myList.get(i).get("deviceName") + "', '" + myList.get(i).get("layerId") + "')");
    	}
	}
	
	public void getUnits(){
		Vector<SoapObject> vectorUnits = SOAPHelper.getUnits();
        ArrayList<HashMap<String, Object>> myList = new ArrayList<HashMap<String, Object>>();
        
    	for(int i=0; i<vectorUnits.size(); i++){
    		HashMap<String, Object> units = new HashMap<String, Object>();
    		units.put("unitId", vectorUnits.get(i).getProperty("unitId"));
    		units.put("unitName", vectorUnits.get(i).getProperty("unitName"));
    		units.put("unitUnit", vectorUnits.get(i).getProperty("unitUnit"));
    		myList.add(units);
    	}

    	for(int i=0; i<myList.size(); i++){
    		dbConn.execSQL("INSERT INTO tbl_Units" +
    				" (unitId, unitName, unitUnit) VALUES" +
    				" ('" + myList.get(i).get("unitId") + "', '" + myList.get(i).get("unitName") + "', '" + myList.get(i).get("unitUnit") + "')");
    	}
	}
	
	public void getInputs(){
		Vector<SoapObject> vectorInputs = SOAPHelper.getInputs();
        ArrayList<HashMap<String, Object>> myList = new ArrayList<HashMap<String, Object>>();
        
    	for(int i=0; i<vectorInputs.size(); i++){
    		HashMap<String, Object> inputs = new HashMap<String, Object>();
    		inputs.put("deviceId", vectorInputs.get(i).getProperty("tbl_Devices_deviceId"));
    		inputs.put("unitUnit", vectorInputs.get(i).getProperty("tbl_Units_unitId"));
    		myList.add(inputs);
    	}

    	for(int i=0; i<myList.size(); i++){
    		dbConn.execSQL("INSERT INTO tbl_Inputs" +
    				" (tbl_Devices_deviceId, tbl_Units_unitId) VALUES" +
    				" ('" + myList.get(i).get("deviceId") + "', '" + myList.get(i).get("unitUnit") + "')");
    	}
	}
	
	public void getOutputs(){
		Vector<SoapObject> vectorOutputs = SOAPHelper.getOutputs();
        ArrayList<HashMap<String, Object>> myList = new ArrayList<HashMap<String, Object>>();
        
    	for(int i=0; i<vectorOutputs.size(); i++){
    		HashMap<String, Object> outputs = new HashMap<String, Object>();
    		outputs.put("deviceId", vectorOutputs.get(i).getProperty("tbl_Devices_deviceId"));
    		outputs.put("unitUnit", vectorOutputs.get(i).getProperty("tbl_Units_unitId"));
    		myList.add(outputs);
    	}

    	for(int i=0; i<myList.size(); i++){
    		dbConn.execSQL("INSERT INTO tbl_Outputs" +
    				" (tbl_Devices_deviceId, tbl_Units_unitId) VALUES" +
    				" ('" + myList.get(i).get("deviceId") + "', '" + myList.get(i).get("tbl_Units_unitId") + "')");
    	}
	}
	
	public void getBuildingsByUser(int userId){
		Vector<SoapObject> vectorBuildings = SOAPHelper.getBuildingsByUser(userId);
        ArrayList<HashMap<String, Object>> myList = new ArrayList<HashMap<String, Object>>();

        if(vectorBuildings != null) {
	    	for(int i=0; i<vectorBuildings.size(); i++){
	    		HashMap<String, Object> buildings = new HashMap<String, Object>();
	    		buildings.put("buildingId", vectorBuildings.get(i).getProperty("buildingId"));
	    		buildings.put("buildingName", vectorBuildings.get(i).getProperty("buildingName"));
	    		buildings.put("lastChange", vectorBuildings.get(i).getProperty("lastChange"));
	    		myList.add(buildings);
	    	}
	    	
	    	for(int i=0; i<myList.size(); i++){
	        	dbConn.execSQL("INSERT INTO tbl_Buildings" +
	    				" (buildingId, buildingName, buildingVersion, tbl_User_userId) VALUES" +
	    				" ('" + myList.get(i).get("buildingId") + "', '" + myList.get(i).get("buildingName") + "', '" + myList.get(i).get("lastChange") + "', '" + userId + "')");
	    		
	    		getLevelsByBuilding(Integer.parseInt((myList.get(i).get("buildingId")).toString()));
	    	}
        }
	}
	
	public void getBuildingById(int buildingId){
		Vector<SoapObject> vectorBuilding = SOAPHelper.getBuildingById(buildingId);

		dbConn.execSQL("INSERT INTO tbl_Buildings" +
				" (buildingId, buildingName, buildingVersion, tbl_User_userId) VALUES" +
				" ('" + vectorBuilding.get(0).getProperty("buildingId") + "', '" + vectorBuilding.get(0).getProperty("buildingName") + "', '" + vectorBuilding.get(0).getProperty("lastChange") + "', '" + vectorBuilding.get(0).getProperty("tbl_User_userId") + "')");
		
		getLevelsByBuilding(Integer.parseInt((vectorBuilding.get(0).getProperty("buildingId")).toString()));
	}
	
	public void getLevelsByBuilding(int buildingId){
		Vector<SoapObject> vectorLevels = SOAPHelper.getLevelsByBuilding(buildingId);
        ArrayList<HashMap<String, Object>> myList = new ArrayList<HashMap<String, Object>>();
        
        if(vectorLevels != null) {
	    	for(int i=0; i<vectorLevels.size(); i++){
	    		HashMap<String, Object> levels = new HashMap<String, Object>();
	    		levels.put("levelId", vectorLevels.get(i).getProperty("levelId"));
	    		levels.put("levelName", vectorLevels.get(i).getProperty("levelName"));
	    		levels.put("levelLevel", vectorLevels.get(i).getProperty("levelLevel"));
	    		myList.add(levels);
	    	}
	
	    	for(int i=0; i<myList.size(); i++){
	    		dbConn.execSQL("INSERT INTO tbl_Levels" +
	    				" (levelId, levelName, levelLevel, tbl_Buildings_buildingId) VALUES" +
	    				" ('" + myList.get(i).get("levelId") + "', '" + myList.get(i).get("levelName") + "', '" + myList.get(i).get("levelLevel") + "', '" + buildingId + "')");
	
	    		getRoomsByLevel(Integer.parseInt((myList.get(i).get("levelId")).toString()));
	    	}
        }
	}
	
	public void getRoomsByLevel(int levelId){
		Vector<SoapObject> vectorRooms = SOAPHelper.getRoomsByLevel(levelId);
        ArrayList<HashMap<String, Object>> myList = new ArrayList<HashMap<String, Object>>();

        if(vectorRooms != null) {
	    	for(int i=0; i<vectorRooms.size(); i++){
	    		HashMap<String, Object> rooms = new HashMap<String, Object>();
	    		rooms.put("roomId", vectorRooms.get(i).getProperty("roomId"));
	    		rooms.put("roomName", vectorRooms.get(i).getProperty("roomName"));
	    		myList.add(rooms);
	    	}
	
	    	for(int i=0; i<myList.size(); i++){
	    		dbConn.execSQL("INSERT INTO tbl_Rooms" +
	    				" (roomId, roomName, tbl_Levels_levelId) VALUES" +
	    				" ('" + myList.get(i).get("roomId") + "', '" + myList.get(i).get("roomName") + "', '" + levelId + "')");
	
	    		getWallsByRoom(Integer.parseInt((myList.get(i).get("roomId")).toString()));
	    	}
        }
	}
	
	public void getWallsByRoom(int roomId){
		Vector<SoapObject> vectorWalls = SOAPHelper.getWallsByRoom(roomId);
        ArrayList<HashMap<String, Object>> myList = new ArrayList<HashMap<String, Object>>();

        if(vectorWalls != null) {
	    	for(int i=0; i<vectorWalls.size(); i++){
	    		HashMap<String, Object> walls = new HashMap<String, Object>();
	    		walls.put("wallId", vectorWalls.get(i).getProperty("wallId"));
	    		walls.put("wallName", vectorWalls.get(i).getProperty("wallName"));
	    		walls.put("wallX1", vectorWalls.get(i).getProperty("wallX1"));
	    		walls.put("wallX2", vectorWalls.get(i).getProperty("wallX2"));
	    		walls.put("wallY1", vectorWalls.get(i).getProperty("wallY1"));
	    		walls.put("wallY2", vectorWalls.get(i).getProperty("wallY2"));
	    		walls.put("wallHeight", vectorWalls.get(i).getProperty("wallHeight"));
	    		walls.put("wallDepth", vectorWalls.get(i).getProperty("wallDepth"));
	    		walls.put("wallType", vectorWalls.get(i).getProperty("wallType"));
	    		myList.add(walls);
	    	}
	
	    	for(int i=0; i<myList.size(); i++){
	    		dbConn.execSQL("INSERT INTO tbl_Walls" +
	    				" (wallId, wallName, wallX1, wallX2, wallY1, wallY2, wallHeight, wallDepth, wallType tbl_Rooms_roomId) VALUES" +
	    				" ('" + myList.get(i).get("wallId") + "', '" + myList.get(i).get("wallName") + "', '" + myList.get(i).get("wallX1") + "', '" + myList.get(i).get("wallX2") + "', '" + myList.get(i).get("wallY1") + "', '" + myList.get(i).get("wallY2") + "', '" + myList.get(i).get("wallHeight") + "', '" + myList.get(i).get("wallDepth") + "', '" + myList.get(i).get("wallType") + "', " + roomId + "')");
	
	    		getInstalledByWall(Integer.parseInt((myList.get(i).get("wallId")).toString()));
	    	}
        }
	}
	
	public void getInstalledByWall(int wallId){
		Vector<SoapObject> vectorInstalled = SOAPHelper.getInstalledByWall(wallId);
        ArrayList<HashMap<String, Object>> myList = new ArrayList<HashMap<String, Object>>();

        if(vectorInstalled != null) {
	    	for(int i=0; i<vectorInstalled.size(); i++){
	    		HashMap<String, Object> installed = new HashMap<String, Object>();
	    		installed.put("installedId", vectorInstalled.get(i).getProperty("installedId"));
	    		installed.put("installedName", vectorInstalled.get(i).getProperty("installedName"));
	    		installed.put("installedX", vectorInstalled.get(i).getProperty("installedX"));
	    		installed.put("installedY", vectorInstalled.get(i).getProperty("installedY"));
	    		installed.put("installedHeight", vectorInstalled.get(i).getProperty("installedHeight"));
	    		installed.put("installedWidth", vectorInstalled.get(i).getProperty("installedWidth"));
	    		installed.put("deviceId", vectorInstalled.get(i).getProperty("tbl_Devices_deviceId"));
	    		myList.add(installed);
	    	}
	
	    	for(int i=0; i<myList.size(); i++){
	    		dbConn.execSQL("INSERT INTO tbl_Installed" +
	    				" (installedId, installedName, installedX, installedY, installedHeight, installedWidth, tbl_Devices_deviceId) VALUES" +
	    				" ('" + myList.get(i).get("installedId") + "', '" + myList.get(i).get("installedName") + "', '" + myList.get(i).get("installedX") + "', '" + myList.get(i).get("installedY") + "', '" + myList.get(i).get("installedHeight") + "', '" + myList.get(i).get("installedWidth") + "', '" + myList.get(i).get("deviceId") + "')");
	
	    		getConsumationsByInstalled(Integer.parseInt((myList.get(i).get("installedId")).toString()));
	    	}
        }
	}
	
	public void getConsumationsByInstalled(int installedId){
		Vector<SoapObject> vectorConsumations = SOAPHelper.getConsumationsByInstalled(installedId);
        ArrayList<HashMap<String, Object>> myList = new ArrayList<HashMap<String, Object>>();

        if(vectorConsumations != null) {
	    	for(int i=0; i<vectorConsumations.size(); i++){
	    		HashMap<String, Object> consumations = new HashMap<String, Object>();
	    		consumations.put("consumationId", vectorConsumations.get(i).getProperty("consumationId"));
	    		consumations.put("consumationValue", vectorConsumations.get(i).getProperty("consumationValue"));
	    		consumations.put("installedId", vectorConsumations.get(i).getProperty("tbl_Installed_installedId"));
	    		consumations.put("unitId", vectorConsumations.get(i).getProperty("tbl_Units_unitId"));
	    		myList.add(consumations);
	    	}
	
	    	for(int i=0; i<myList.size(); i++){
	    		dbConn.execSQL("INSERT INTO tbl_Consumations" +
	    				" (consumationId, consumationValue, tbl_Installed_installedId, tbl_Units_unitId) VALUES" +
	    				" ('" + myList.get(i).get("consumationId") + "', '" + myList.get(i).get("consumationValue") + "', '" + myList.get(i).get("installedId") + "', '" + myList.get(i).get("unitId") + "')");
	    	}
        }
	}
}